package com.chinasofti.dao;

import com.chinasofti.domain.Student;
import com.chinasofti.util.JDBCtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StudentDaoImpl implements StudentDao {
    //根据工具JDBCUtils类改造 StudentDaoImpl中的方法
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement stat = null;
        ResultSet rs   = null;

        try {
            con = JDBCtils.getConnection();
            //获取执行对象
            stat =  con.createStatement();
            //执行sql语句
            String sql = "SELECT * FROM student";
            rs = stat.executeQuery(sql);
            while(rs.next()){
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                //封装成student对象
                Student stu = new Student(sid,name,age,birthday);
                //将student存储到集合中
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //释放资源
            JDBCtils.close(con,stat,rs);
        }
        return list;
    }
    //根据条件查询，根据id查询学生信息   findById(Integer id)
    @Override
    public Student findById(Integer id) {
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        Student stu =null;
        try {
            con = JDBCtils.getConnection();
            stat = con.createStatement();
            String sql = "select * from student where sid= "+id;
            rs = stat.executeQuery(sql);
            while(rs.next()){
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                stu = new Student(sid,name,age,birthday);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCtils.close(con,stat,rs);
        }
        return stu;
    }


    @Override
    public int insert(Student stu) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try {
            con = JDBCtils.getConnection();
            //获取执行对象
            stat =  con.createStatement();
            //执行sql语句
            Date d = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(d);
            String sql =
                    "INSERT INTO student " +
                            "(name,age,birthday) VALUES ('"+stu.getName()+"',"+stu.getAge()+",'"+birthday+"')";
            result = stat.executeUpdate(sql);// 增删改用executeUpdate
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCtils.close(con,stat);
        }
        return result;
    }

    @Override
    public int update(Student stu) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try {
            con = JDBCtils.getConnection();
            //获取执行对象
            stat =  con.createStatement();
            //执行sql语句
            Date d = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(d);
            String sql ="update student set name ='"+stu.getName()+"',age = "+stu.getAge()+",birthday ='"+birthday+"' where sid = "+stu.getSid();
            result = stat.executeUpdate(sql);// 增删改用executeUpdate
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCtils.close(con,stat);
        }
        return result;


    }

    @Override
    public int delete(int id) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        try {
            con = JDBCtils.getConnection();
            //获取执行对象
            stat =  con.createStatement();
            //执行sql语句
            String sql ="DELETE from student  where sid = "+id;
            result = stat.executeUpdate(sql);// 增删改用executeUpdate
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCtils.close(con,stat);
        }
        return result;
    }

}